﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using UnityEngine;
using LitJson;
using System.Text.RegularExpressions;

public class ExicleToJson : MonoBehaviour
{
    #region Variable
    public DataTable dataTable = new DataTable();
    FileStream fs;
    string path_playerData;
    #endregion

    #region UnityMethods
    void Start()
    {
        path_playerData = Application.streamingAssetsPath + "/PlayerData.xlsx";
        ExicleToDataTable("单选题", true);
    }

    void Update()
    {

    }


    public DataTable ExicleToDataTable(string sheelName, bool isFirstRowColumn)
    {
        dataTable.Clear();
        ISheet sheet = null;
        int startRow = 0;//开始行
        try
        {
            fs = new FileStream(path_playerData, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = null;
        if (path_playerData.IndexOf(".xlsx") > 0)//2007
        {
            print("2007版本的Exicle");
            workbook = new XSSFWorkbook(fs);
            fs.Close();
            fs.Dispose();
        }
        if (sheelName != null)
        {
            sheet = workbook.GetSheet(sheelName);//找到对应的工作单赋值
            if (sheet == null)//如果该名称的工作单不存在
            {
                sheet = workbook.GetSheetAt(0);//赋给第一张工作单
            }
        }
        else//如果该名称为空
        {
            sheet = workbook.GetSheetAt(0);//赋给第一张工作单
        }

        if (sheet != null)
        {
            IRow firstRow = sheet.GetRow(0);
            int count_cell = firstRow.LastCellNum;//一行最后一个cell的编号 即总的列数

            if (isFirstRowColumn)
            {
                for (int i = firstRow.RowNum; i < count_cell; i++)//处理第一行的所有单元格
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null)
                    {
                        string callValue = cell.StringCellValue;//到这里已经能取到表格的数据了
                      //  print(callValue);//列的名字
                        if (callValue != null)//把第一行的所有标题添加进dataTable中
                        {
                            DataColumn column = new DataColumn(callValue);
                            dataTable.Columns.Add(column);
                        }
                    }
                }
                startRow = sheet.FirstRowNum + 1;//由于第一行是数据类型，所以数据要从第二行开始算
            }
            else//如果不需要第一行菜单栏
            {
                startRow = sheet.FirstRowNum;
            }


            int count_row = sheet.LastRowNum;
            for (int i = startRow; i <= count_row; i++)//添加所有的表格数据
            {
                DataRow dataRow = dataTable.NewRow();//new一个数据行

                IRow row = sheet.GetRow(i);
                if (row == null) continue;

                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                       // print(row.GetCell(j).ToString());//获取单元格的文本内容
                    }

                }

                dataTable.Rows.Add(dataRow);
            }
        }
       // print(dataTable.Rows.Count);
        return dataTable;
    }
        catch (Exception ex)
        {
            print("捕获异常：" + ex);
            return null;
        }
    }

    private Dictionary<string, int> _CorrectAnswer = new Dictionary<string, int>() { { "A", 0 }, { "B", 1 }, { "C", 2 }, { "D", 3 } };
   [ContextMenu("DataTableToJson")]
    public void DataTableToJson() 
    {
        // Debug.Log(dataTable.Rows[a][b].ToString());
      //  Debug.Log(dataTable.Rows.Count);
        OnLineTestJsonDataRoot od = new OnLineTestJsonDataRoot();
        od.TotalExaminationTime = 60;
        od.Topic = new List<TopicItem>();
        
        for (int i = 0; i < dataTable.Rows.Count-2; i++)
        {
            TopicItem ti = new TopicItem();
            ti.Id = i;
            ti.Exam = "单选题";
            //ti.Title = dataTable.Rows[i][2].ToString();
            ti.Title = Regex.Unescape(dataTable.Rows[i][2].ToString());
            List<string> Answer = new List<string>();
            Answer.Clear();
            for (int j = 3; j < 7; j++)
            {
             
                //Debug.Log(dataTable.Rows[i][j].ToString() + "-------------" + i.ToString()+ "-------------" + j.ToString());
                if (!string.IsNullOrEmpty(dataTable.Rows[i][j].ToString())) 
                {
                    //Regex r = new Regex("ABCD");
                
                    string cc = dataTable.Rows[i][j].ToString();
                    //  Match m = r.Match(cc);
                    cc= cc.Remove(0,1);
                    Answer.Add(cc);
                   // Answer.Add(dataTable.Rows[i][j].ToString()) ;
                }
            }
            ti.Answer = Answer;
            string arg = Regex.Unescape(dataTable.Rows[i][7].ToString()) ;
            Debug.Log(arg);
           ti.CorrectAnswer = _CorrectAnswer[arg] ;
            ti.Parsing = "解析:";
            ti.Score = 2;
            od.Topic.Add(ti);
        }
        Debug.Log(Regex.Unescape(JsonMapper.ToJson(od)));  

    }
    public int a,b;
    [ContextMenu("TEST")]
    public void TEST() 
    {
        Debug.Log(dataTable.Rows[a][b].ToString());
    }
    #endregion

    #region PrivateMethods

    #endregion

    #region PublicMethods

    #endregion
}
